package com.install.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.mchange.v2.c3p0.impl.NewProxyResultSet;


public class DbUtil extends BaseJdbcDao {

	private DbUtil(){ }
	
	public static int update(String sql){
		int count = 0;
		openConnection();
		try {
			statement = connection.createStatement();
			count = statement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return count;
	}
	
	public static <T> List<T> query(T t, String sql){
		List<T> list = new ArrayList<T>();
		
		Class cls = t.getClass();
		Field[] fields = cls.getDeclaredFields();
		
		openConnection();
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			while(resultSet.next()){
				T object = (T)cls.newInstance();
				for(int i=0; i<fields.length; i++){
					fields[i].setAccessible(true);
					String typeName = fields[i].getType().getName();
					int point = typeName.lastIndexOf(".");
					String strType = "";
					if(point == -1){
						strType = String.valueOf(typeName.charAt(0)).toUpperCase()
												+typeName.substring(1);
					}else {
						strType = typeName.substring(point+1);
					}
					EnumType mt = EnumType.valueOf(strType);
					switch(mt){
					case String:
						fields[i].set(object, resultSet.getString(fields[i].getName()));
						break;
					case Integer:
					case Int:
						fields[i].set(object, resultSet.getInt(fields[i].getName()));
						break;
					case Double:
						fields[i].set(object, resultSet.getDouble(fields[i].getName()));
						break;
					case Date:
						fields[i].set(object, resultSet.getDate(fields[i].getName()));
						break;
					case Boolean:
						fields[i].set(object, resultSet.getBoolean(fields[i].getName()));
						break;
					}
				}
				list.add(object);
			}
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return list;
	}
	public static void main(String[] args) {
		DataSourceTransactionManager dtm=null;
		TransactionStatus ts=null;
		try {
			ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
		    System.out.println(ac.getBean("txManager"));
		    DefaultTransactionDefinition definition=new DefaultTransactionDefinition();
		    dtm=(DataSourceTransactionManager)ac.getBean("txManager");
		    ts=dtm.getTransaction(definition);
		    Connection conn=dtm.getDataSource().getConnection();
		    JdbcTemplate jdbcTemplate = new JdbcTemplate(dtm.getDataSource());
		    //jdbcTemplate.update("INSERT INTO user VALUES (4,'管理员4','111111',NULL,1,'1','1')");
		   // jdbcTemplate.update("INSERT INTO user VALUES (2,'管理员2','111111',NULL,1,'1','1')");
		    //jdbcTemplate.update("INSERT INTO user VALUES (3,'管理员3','111111',NULL,1,'1','1')");
		    List list=jdbcTemplate.query("select uid,uname,password from user", new RowMapper(){  
		    	     public Object mapRow(ResultSet rs, int index) throws SQLException{
		    	    	 JSONObject json=new JSONObject();
		    	    	 //json.element("uid", rs.getString("uid"));
		    	    	 //System.out.println(index+rs.getString(index+1));
		    	    	int count=rs.getMetaData().getColumnCount();
		    	    	 for(int i=count;i>0;i--){
		    	    		 String name=rs.getMetaData().getColumnName(i);
		    	    		 json.element(name,rs.getString(name));
		    	    	 }
		    	    	 //System.out.println(rs.getMetaData().getColumnName(index+1));
		    	    	 //json.element(rs.getCursorName(), rs.getObject(index));
		    	    	 return json;
		    	     }
		    });
		    System.out.println(list);
		    dtm.commit(ts);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			dtm.rollback(ts); //ts.setRollbackOnly();
			e.printStackTrace();
		}
		


	}
}
